<?php

App::uses('AppModel', 'Model');
App::uses('LogsDao','Model');

define('CAMPOS_TABLA_SORTEOS','id,nom,imatge,text_desc,text_data,data_inici,data_final,data_alta,temporada,ordre,actiu');

class SortejosDao extends AppModel {
	
	public $useTable = false;
	var $logsDao;
	
	function __construct(){
		$this->logsDao = new LogsDao();
		
	}
	
	
	
	public function getSortejosActius(){
		$queryString = "SELECT ".CAMPOS_TABLA_SORTEOS." FROM sortejos WHERE actiu=1 ORDER BY ordre ASC";
		try {
			return $this->query($queryString);
		}catch (Exception $e) {
			$this->logsDao->insert("error getSortejosActius",$e->getMessage());
		}
	}
	
	public function getCountSortejosActius(){
		$queryString = "SELECT count(id) as total FROM sortejos WHERE actiu=1";
		try {
			return $this->query($queryString);
		}catch (Exception $e) {
			$this->logsDao->insert("error getCountSortejosActius",$e->getMessage());
		}
	}
	
	public function getCountSortejos(){
		$queryString = "SELECT count(id) as total FROM sortejos";
		try {
			$returnValue = $this->query_row($queryString);
			return $returnValue[0]->total;
		}catch (Exception $e) {
			$this->logsDao->insert("error getCountSortejos",$e->getMessage());
		}
	}
	
	public function getCountTotalGuanyadors(){
		$queryString = "SELECT count(id) as total FROM historial_premiats";
		try {
			$returnValue = $this->query_row($queryString);
			return $returnValue[0]->total;
		}catch (Exception $e) {
			$this->logsDao->insert("error getCountTotalGuanyadors",$e->getMessage());
		}
	}

	
	public function getCountCodisJugats(){
		$queryString = "SELECT count(id) as total FROM codis_jugats";
		try {
			$returnValue = $this->query($queryString);
			return $returnValue[0]->total;
		}catch (Exception $e) {
			$this->logsDao->insert("error getCountCodisJugats",$e->getMessage());
		}
	}
	
	
	public function getAllSortejos(){
		$queryString = "SELECT ".CAMPOS_TABLA_SORTEOS." FROM sortejos ORDER BY data_alta";
		
		try {
			return $this->query($queryString);
		}catch (Exception $e) {
			$this->logsDao->insert("error getAllSortejos",$e->getMessage());
		}
	}
	
	public function getAllSortejosPerTemporada($temporada){
		$queryString = "SELECT ".CAMPOS_TABLA_SORTEOS." FROM sortejos WHERE temporada=".$temporada;
		try {
			return $this->query($queryString);
		}catch (Exception $e) {
			$this->logsDao->insert("error getAllSortejosPerTemporada",$e->getMessage());
		}
	}
	
	public function getGuanyadorsReservesPerSorteig($id_sorteig,$total){
		$queryString = "SELECT t1.id, t1.idusuari, t1.codi, t1.sorteig, t1.data_alta, t2.mail,t2.nom, t2.cognoms, t2.data_naixement, t2.club
    				  FROM codis_jugats t1
    				  LEFT JOIN usuarios t2 ON t1.idusuari = t2.idusuario
    				  WHERE sorteig =".$id_sorteig." ORDER BY RAND() LIMIT ".$total;
		try {
			return $this->query_row($queryString);
		}catch (Exception $e) {
			$this->logsDao->insert("error getGuanyadorsReservesPerSorteig",$e->getMessage());
		}
	}
	
	public function getCodisJugatsPerSetmana(){
		$queryString = "SELECT COUNT(*) AS total,data_alta AS fecha FROM codis_jugats GROUP BY WEEK(data_alta)";
		try {
			return $this->query_row($queryString);
		}catch (Exception $e) {
			$this->logsDao->insert("error getCodisJugatsPerSetmana",$e->getMessage());
		}
	}
	
	
	public function getSortejosPerActiu($actiu){
		$queryString = "SELECT ".CAMPOS_TABLA_SORTEOS." FROM sortejos WHERE actiu=".$actiu;
		
		try {
			return $this->query($queryString);
		}catch (Exception $e) {
			$this->logsDao->insert("error getSortejosPerActiu",$e->getMessage());
		}
	}
	
	public function getInfoSorteigPerId($id){
		$queryString = "SELECT * FROM sortejos WHERE id=".$id;

		try {
			return $this->query($queryString);
		}catch (Exception $e) {
			$this->logsDao->insert("error getInfoSorteigPerId",$e->getMessage());
		}
	}
    
	
    public function updateNomUsuari($id,$usuari){
    	$queryString = "UPDATE usuaris SET usuari = '".$usuari."' WHERE id =".$id;
    	return returnValueTrue($queryString);
    }
    
    function deleteSorteigPerId($id){
    	$queryString = "DELETE FROM sortejos WHERE id = '".$id."' ";
    	try {
    		$this->execute($queryString);
    		return true;
    	}catch (Exception $e) {
    		$this->logsDao->insert("error deleteSorteigPerId",$e->getMessage());
    		return false;
    	}
    	
    }
    
    
    public function getSortejosPerBusquedaNom($nom){
    	 
    	$queryString="SELECT ".CAMPOS_TABLA_SORTEOS." FROM sortejos WHERE nom LIKE '%".$nom."%'" ;

    	try {
    		$returnValue = $this->query($queryString);
    		return $returnValue;
    	}catch (Exception $e) {
    		$this->logsDao->insert("error getSortejosPerBusquedaNom",$e->getMessage());
    	}
    	 
    }

    
    function crearNuevoSorteo($info){
    	$ultimoOrdre = $this->getUltimoOrdrePerSorteigActiu();
    	$queryString = "INSERT INTO sortejos (".CAMPOS_TABLA_SORTEOS.")
    				    VALUES (null,'".$info['nom_sorteig']."','".$info['imatge']."','".$info['text_desc']."','".$info['text_data']."','".$info['data_inici']."','".$info['data_final']."',CURRENT_TIMESTAMP,'".TEMPORADA."',$ultimoOrdre+1,'".$info['actiu']."');";
    	try {
    		$this->execute($queryString);
    		return true;
    	}catch (Exception $e) {
    		$this->logsDao->insert("error crearNuevoSorteo",$e->getMessage());
    		return false;
    	}
    }
   
    function getUltimoOrdrePerSorteigActiu(){
    	$queryString="SELECT ordre FROM sortejos WHERE actiu=1 ORDER BY ordre DESC LIMIT 1" ;
    	try {
    		$returnValue = $this->query($queryString);
    		return $returnValue[0]->ordre;
    	}catch (Exception $e) {
    		$this->logsDao->insert("error getUltimoOrdrePerSorteigActiu",$e->getMessage());
    	}
    }
    
    function get_codis_jugats_sorteig($id){
    	$queryString="SELECT t1.id, t1.idusuari, t1.codi, t1.sorteig, t1.data_alta, t2.mail,t2.nom, t2.cognoms, t2.data_naixement, t2.club
    				  FROM codis_jugats t1
    				  LEFT JOIN usuarios t2 ON t1.idusuari = t2.idusuario
    				  WHERE sorteig = ".$id;
    	try {
    		$returnValue = $this->query_row($queryString);
    		return $returnValue;
    	}catch (Exception $e) {
    		$this->logsDao->insert("error get_codis_jugats_sorteig",$e->getMessage());
    	}
    }
    
    function getHistorialPremiadosPorSorteo($id_sorteo){
    	
    	$queryString = "SELECT t1.idusuario,t1.codi_premiat,t2.nom,t2.cognoms,t2.mail,t2.data_naixement,t2.club,t1.estat FROM historial_premiats t1 LEFT JOIN usuarios t2 ON t1.idusuario=t2.idusuario WHERE t1.id_sorteig=".$id_sorteo;
    	
    	try {
    		$returnValue = $this->query_row($queryString);
    		return $returnValue;
    	}catch (Exception $e) {
    		$this->logsDao->insert("error getHistorialPremiadosPorSorteo",$e->getMessage());
    	}
    }
    
    function returnValueTrue($queryString){ // ejecuta un update si es OK devuelve true , si no inserta log (hacer algo mas?)
    	try {
    		$this->query($queryString);
    		return true;
    	}catch (Exception $e) {
    		$this->logsDao->insert("error",$e->getMessage());
    	}
    }
    
    function insertGanadoresSorteo($queryString){
    	try {
    		$this->execute($queryString);
    		return true;
    	}catch (Exception $e) {
    		$this->logsDao->insert("error insertGanadoresSorteo",$e->getMessage());
    	}
    }
    
    function updateCampActiu($id,$actiu){
    	if($actiu == 'false'){
    		$queryString = "UPDATE sortejos SET ordre=NULL ,actiu = ".$actiu." WHERE id=".$id;
    	}else{
    		$queryString = "UPDATE sortejos actiu = ".$actiu." WHERE id=".$id;
    	}

    	try {
    		$this->execute($queryString);
    		return true;
    	}catch (Exception $e) {
    		$this->logsDao->insert("error updateCampActiu",$e->getMessage());
    	}
    }
   
    
    function updateCampSorteig($id,$campo,$valor_campo){
    	$queryString = "UPDATE sortejos SET ".$campo." = '".$valor_campo."' WHERE id=".$id;
    	try {
    		$this->execute($queryString);
    		return true;
    	}catch (Exception $e) {
    		$this->logsDao->insert("error updateCampSorteig",$e->getMessage());
    	}
    }
    
    
    
}



?>